﻿using System;
using System.Collections.Generic;
using System.Data;
using ContractManager.Entity;
using Speed.Data;
namespace ContractManager.BLL.Business
{
    public static class ClsFieldTables
    {
        public static List<FieldTables> SelectAll()
        {
            using (var db = Sys.NewDb())
            {
                return db.Select<FieldTables>();
                
            }
            
        }

        public static void Insert(FieldTables capital)
        {
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                db.Insert<FieldTables>(capital, EnumSaveMode.Requery);

                foreach (FieldTableDetails addItem in capital.ListAdd)
                {
                    addItem.FieldTableId = capital.FieldTableId;
                    db.Insert<FieldTableDetails>(addItem, EnumSaveMode.Requery);
                }
                foreach (FieldTableDetails updateItem in capital.ListUpdate)
                {
                    updateItem.FieldTableId = capital.FieldTableId;
                    db.Update<FieldTableDetails>(updateItem);
                }
                foreach (FieldTableDetails delItem in capital.ListDelete)
                {
                    db.Delete<FieldTableDetails>(string.Format("FieldTableDetailId={0}", delItem.FieldTableDetailId));
                }

                
                db.Commit();
            }

        }

        public static void Update(FieldTables capital)
        {
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                db.Update<FieldTables>(capital);
                foreach (FieldTableDetails addItem in capital.ListAdd)
                {
                    addItem.FieldTableId = capital.FieldTableId;
                    db.Insert<FieldTableDetails>(addItem, EnumSaveMode.Requery);
                }
                foreach (FieldTableDetails updateItem in capital.ListUpdate)
                {
                    updateItem.FieldTableId = capital.FieldTableId;
                    db.Update<FieldTableDetails>(updateItem);
                }
                foreach (FieldTableDetails delItem in capital.ListDelete)
                {
                    db.Delete<FieldTableDetails>(string.Format("FieldTableDetailId={0}", delItem.FieldTableDetailId));
                }
               
                db.Commit();
            }

        }

        public static FieldTables SelectByPk(Int32 capId)
        {
            using (var db = Sys.NewDb())
            {
                return db.SelectSingle<FieldTables>(string.Format("FieldTableId={0}", capId));
            }
            
        }

        public static List<FieldTables> SelectByForm(Int32 formNo)
        {
            using (var db = Sys.NewDb())
            {
                return db.Select<FieldTables>(string.Format("FormNo={0}", formNo));
            }

        }

        public static FieldTables SelectByFormAndFieldName(string fieldTableName, Int32 formNo)
        {
            using (var db = Sys.NewDb())
            {
                return db.SelectSingle<FieldTables>(string.Format("FieldTableName='{0}' AND FormNo={1}",fieldTableName, formNo));
            }

        }

        public static List<FieldTableDetails> SelectDetails(Int32 masterId)
        {
            using (var db = Sys.NewDb())
            {
                return db.Select<FieldTableDetails>(string.Format("FieldTableId={0} AND FieldTableType IN (6,10)", masterId));
            }

        }

        public static List<FieldTableDetails> SelectDetails(Int32 masterId,string parentMember)
        {
            using (var db = Sys.NewDb())
            {
                return db.Select<FieldTableDetails>(string.Format("FieldTableId={0} AND FieldTableDetailGetValueFromColumn='{1}'", masterId, parentMember));
            }

        }

        public static DataTable ListDetail(Int32 masterId)
        {
            string commandText = String.Format("SELECT * FROM [Contract].[FieldTableDetails] WHERE FieldTableId= {0}", masterId);
            using (var db = Sys.NewDb())
            {
                return db.ExecuteDataTable(commandText);
            }
        }


        public static DataTable ListAll()
        {
            const string commandText = "EXEC dbo.usp_FieldTables_GetAll";
            using (var db = Sys.NewDb())
            {
                return db.ExecuteDataTable(commandText);
            }
        }


        public static List<FieldTableDetails> SelectFormulaColumn(Int32 masterId)
        {
            using (var db = Sys.NewDb())
            {
                return db.Select<FieldTableDetails>(string.Format("FieldTableId={0} AND FieldTableType=3 AND LEN(ISNULL(FieldTableDetailFormula,''))>0", masterId));
            }

        }

        public static int DeleteByPk(Int32 capId)
        {
            using (var db = Sys.NewDb())
            {
                return db.Delete<FieldTables>(string.Format("FieldTableId={0}", capId));
            }
            
        }
    }
}